{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests,json,pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getStatsQueryData(url):\n",
    "    kv = {'user-agent': 'Mozilla/5.0'}\n",
    "    js = json.loads(requests.get(url, headers=kv).text)\n",
    "    reg = {r[\"code\"]: r[\"cname\"] for r in js[\"returndata\"][\"wdnodes\"][1][\"nodes\"]}\n",
    "    sj = {r[\"code\"]: r[\"cname\"] for r in js[\"returndata\"][\"wdnodes\"][2][\"nodes\"]}\n",
    "    v={}\n",
    "    for x in js[\"returndata\"][\"datanodes\"]:\n",
    "        name = reg[x[\"wds\"][1][\"valuecode\"]]\n",
    "        code = x[\"wds\"][1][\"valuecode\"]\n",
    "        if name in v:\n",
    "            v[name][sj[x[\"wds\"][2][\"valuecode\"]]]=x[\"data\"][\"data\"]\n",
    "        else:\n",
    "            v[name] = {\"name\":name,\"code\":code,\n",
    "                       sj[x[\"wds\"][2][\"valuecode\"]]:x[\"data\"][\"data\"]}\n",
    "    return (js[\"returndata\"][\"wdnodes\"][0][\"nodes\"][0][\"cname\"],\n",
    "           pandas.DataFrame([v[k] for k in v]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"http://data.stats.gov.cn/easyquery.htm?\\\n",
    "m=QueryData&dbcode=fsnd&rowcode=reg&colcode=sj&\\\n",
    "wds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22\\\n",
    "A0K0101%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22sj%22%2C%22\\\n",
    "valuecode%22%3A%22LAST20%22%7D%5D&k1=1570764644918\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "name,data = getStatsQueryData(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>code</th>\n",
       "      <th>2018年</th>\n",
       "      <th>2017年</th>\n",
       "      <th>2016年</th>\n",
       "      <th>2015年</th>\n",
       "      <th>2014年</th>\n",
       "      <th>2013年</th>\n",
       "      <th>2012年</th>\n",
       "      <th>2011年</th>\n",
       "      <th>...</th>\n",
       "      <th>2008年</th>\n",
       "      <th>2007年</th>\n",
       "      <th>2006年</th>\n",
       "      <th>2005年</th>\n",
       "      <th>2004年</th>\n",
       "      <th>2003年</th>\n",
       "      <th>2002年</th>\n",
       "      <th>2001年</th>\n",
       "      <th>2000年</th>\n",
       "      <th>1999年</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>北京市</td>\n",
       "      <td>110000</td>\n",
       "      <td>0</td>\n",
       "      <td>5129.81</td>\n",
       "      <td>5070.00</td>\n",
       "      <td>4605.00</td>\n",
       "      <td>4608.00</td>\n",
       "      <td>4794.68</td>\n",
       "      <td>5149.00</td>\n",
       "      <td>5416.00</td>\n",
       "      <td>...</td>\n",
       "      <td>4459.13</td>\n",
       "      <td>4580</td>\n",
       "      <td>4026.3</td>\n",
       "      <td>3618.91</td>\n",
       "      <td>3173.43</td>\n",
       "      <td>1903.54</td>\n",
       "      <td>3115</td>\n",
       "      <td>2945.99</td>\n",
       "      <td>2768.00</td>\n",
       "      <td>2496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>天津市</td>\n",
       "      <td>120000</td>\n",
       "      <td>0</td>\n",
       "      <td>3751.47</td>\n",
       "      <td>3556.87</td>\n",
       "      <td>3298.11</td>\n",
       "      <td>2992.10</td>\n",
       "      <td>2591.28</td>\n",
       "      <td>2226.41</td>\n",
       "      <td>1755.53</td>\n",
       "      <td>...</td>\n",
       "      <td>1001.39</td>\n",
       "      <td>779</td>\n",
       "      <td>625.9</td>\n",
       "      <td>509.01</td>\n",
       "      <td>412.53</td>\n",
       "      <td>329.47</td>\n",
       "      <td>342</td>\n",
       "      <td>280.17</td>\n",
       "      <td>231.76</td>\n",
       "      <td>209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>河北省</td>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>578.69</td>\n",
       "      <td>552.41</td>\n",
       "      <td>501.91</td>\n",
       "      <td>534.19</td>\n",
       "      <td>585.78</td>\n",
       "      <td>544.94</td>\n",
       "      <td>447.65</td>\n",
       "      <td>...</td>\n",
       "      <td>273.95</td>\n",
       "      <td>309</td>\n",
       "      <td>243.1</td>\n",
       "      <td>209.17</td>\n",
       "      <td>190.42</td>\n",
       "      <td>84.60</td>\n",
       "      <td>167</td>\n",
       "      <td>156.61</td>\n",
       "      <td>141.90</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>山西省</td>\n",
       "      <td>140000</td>\n",
       "      <td>0</td>\n",
       "      <td>350.14</td>\n",
       "      <td>317.38</td>\n",
       "      <td>297.10</td>\n",
       "      <td>280.73</td>\n",
       "      <td>822.68</td>\n",
       "      <td>720.24</td>\n",
       "      <td>567.19</td>\n",
       "      <td>...</td>\n",
       "      <td>300.65</td>\n",
       "      <td>222</td>\n",
       "      <td>164.2</td>\n",
       "      <td>116.22</td>\n",
       "      <td>81.23</td>\n",
       "      <td>36.27</td>\n",
       "      <td>75</td>\n",
       "      <td>59.47</td>\n",
       "      <td>49.91</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>150000</td>\n",
       "      <td>0</td>\n",
       "      <td>1245.56</td>\n",
       "      <td>1139.03</td>\n",
       "      <td>962.49</td>\n",
       "      <td>1002.96</td>\n",
       "      <td>962.29</td>\n",
       "      <td>771.96</td>\n",
       "      <td>670.97</td>\n",
       "      <td>...</td>\n",
       "      <td>577.19</td>\n",
       "      <td>545</td>\n",
       "      <td>403.7</td>\n",
       "      <td>352.07</td>\n",
       "      <td>253.30</td>\n",
       "      <td>138.45</td>\n",
       "      <td>149</td>\n",
       "      <td>137.40</td>\n",
       "      <td>126.45</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     name    code  2018年    2017年    2016年    2015年    2014年    2013年  \\\n",
       "0     北京市  110000      0  5129.81  5070.00  4605.00  4608.00  4794.68   \n",
       "1     天津市  120000      0  3751.47  3556.87  3298.11  2992.10  2591.28   \n",
       "2     河北省  130000      0   578.69   552.41   501.91   534.19   585.78   \n",
       "3     山西省  140000      0   350.14   317.38   297.10   280.73   822.68   \n",
       "4  内蒙古自治区  150000      0  1245.56  1139.03   962.49  1002.96   962.29   \n",
       "\n",
       "     2012年    2011年  ...    2008年  2007年   2006年    2005年    2004年    2003年  \\\n",
       "0  5149.00  5416.00  ...  4459.13   4580  4026.3  3618.91  3173.43  1903.54   \n",
       "1  2226.41  1755.53  ...  1001.39    779   625.9   509.01   412.53   329.47   \n",
       "2   544.94   447.65  ...   273.95    309   243.1   209.17   190.42    84.60   \n",
       "3   720.24   567.19  ...   300.65    222   164.2   116.22    81.23    36.27   \n",
       "4   771.96   670.97  ...   577.19    545   403.7   352.07   253.30   138.45   \n",
       "\n",
       "   2002年    2001年    2000年  1999年  \n",
       "0   3115  2945.99  2768.00   2496  \n",
       "1    342   280.17   231.76    209  \n",
       "2    167   156.61   141.90    124  \n",
       "3     75    59.47    49.91     43  \n",
       "4    149   137.40   126.45    120  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "nc = {}\n",
    "for c in data.columns:\n",
    "    if \"年\" in c:\n",
    "        nc[c] = \"y{0}\".format(c.split(\"年\")[0])\n",
    "data.rename(columns=nc,inplace=True) \n",
    "data = data.drop([\"y2018\"],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(\"./data/旅游收入.csv\",index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
